#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "formsqlconnect.h"
#include "formmysql.h"
#include "formmongodb.h"
#include "formredis.h"
#include "formgaussdb.h"
#include<QTableView>
#include<QStandardItem>
#include<QStandardItemModel>
#include <QtCharts>
#include <QtSql>
#include<QDebug>
#include <QApplication>
#include <QChartView>
#include<QSqlQuery>
#include<QString>
#include<QSqlQueryModel>
#include<QAbstractItemModel>
#include<QChar>
#include <QSqlDatabase>
#include<QDebug>
#include<QSqlQuery>
#include<QMessageBox>
#include<QLineEdit>
#include<QString>

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    ui->stackedWidget->hide();

    //创建用于创建表的表
    QStandardItemModel *page_1tableforcretable=new QStandardItemModel(ui->tableViewCreatetable);
    page_1tableforcretable->setHorizontalHeaderItem(0,new QStandardItem("字段名"));
    page_1tableforcretable->setHorizontalHeaderItem(1,new QStandardItem("数据类型"));
    page_1tableforcretable->setHorizontalHeaderItem(2,new QStandardItem("数据长度"));
    page_1tableforcretable->setHorizontalHeaderItem(3,new QStandardItem("空"));
    page_1tableforcretable->setHorizontalHeaderItem(4,new QStandardItem("主键"));
    page_1tableforcretable->setHorizontalHeaderItem(5,new QStandardItem("外键/表名"));
    page_1tableforcretable->setHorizontalHeaderItem(6,new QStandardItem("外键/字段"));
    page_1tableforcretable->setRowCount(1);
    ui->tableViewCreatetable->setModel(page_1tableforcretable);

}

MainWindow::~MainWindow()
{
    delete ui;
}


void MainWindow::on_actionSQLServer_triggered()    //点击主窗口的连接下的SQLSERVER，打开连接窗口
{
    Formsqlconnect *sqlconnect= new Formsqlconnect;
    sqlconnect->show();
    ui->lineEditshowdatabasenow->setText("SQL Server");
}

void MainWindow::on_actionMysql_triggered()       //点击主窗口的连接下的Mysql，打开连接窗口
{
    Formmysql *mysqlconnect= new Formmysql;
    mysqlconnect->show();
    ui->lineEditshowdatabasenow->setText("Mysql");
}

void MainWindow::on_actionMongoDB_triggered()     //点击主窗口的连接下的Mongodb，打开连接窗口
{
    Formmongodb *mongodbconnect=new Formmongodb;
    mongodbconnect->show();
    ui->lineEditshowdatabasenow->setText("MongoDB");
}

void MainWindow::on_actionRedis_triggered()        //点击主窗口的连接下的Redis，打开连接窗口
{
    Formredis *redisconnect=new Formredis;
    redisconnect->show();
    ui->lineEditshowdatabasenow->setText("Redis");
}

void MainWindow::on_actionGaussDB_triggered()       //点击主窗口的连接下的GaussDB，打开连接窗口
{
    Formgaussdb *gaussdbconnect=new Formgaussdb;
    gaussdbconnect->show();
    ui->lineEditshowdatabasenow->setText("GaussDB");
}

void MainWindow::on_actionCreatedatabase_triggered()     //为菜单“新建-数据库”添加事件，打开对应界面
{
    ui->stackedWidget->show();                 //显示配件只能一级一级地展示
    ui->stackedWidget->setCurrentIndex(0);//stackedwidget可以显示多页面，通过参数找到指定页面，0是第一页

}

void MainWindow::on_actionCreatetable_triggered()    //为菜单“新建-表”添加事件，打开对应界面
{
    ui->stackedWidget->show();
    ui->stackedWidget->setCurrentIndex(1);
}

void MainWindow::on_actionSelectdepend_triggered()  //为菜单“查询-按需查询”添加事件，打开对应界面
{
    ui->stackedWidget->show();
    ui->stackedWidget->setCurrentIndex(2);
}

void MainWindow::on_actionUpdatedata_triggered()    //为菜单“更新-更新数据”添加事件，打开对应界面
{
    ui->stackedWidget->show();
    ui->stackedWidget->setCurrentIndex(3);
}

void MainWindow::on_actionDeleteobject_triggered()   //为菜单“删除-删除对象”添加事件，打开对应界面
{
    ui->stackedWidget->show();
    ui->stackedWidget->setCurrentIndex(6);
}

void MainWindow::on_actionzhexian_triggered()    //菜单“图表”
{
    QString select = ui->lineEditselect->text();
    QString from = ui->lineEditfrom->text();
    QString where = ui->lineEditwhere->text();
    QString inquery = QString("select %1 from %2 where %3").arg(select).arg(from).arg(where);
    QSqlQuery query(inquery);
    QList<QPointF> points;
    while (query.next()) {
        double x = query.value("x").toDouble();
        double y = query.value("y").toDouble();
        points.append(QPointF(x, y));
    }

    QChart *chart = new QChart();

    QLineSeries *lineSeries = new QLineSeries();
    QBarSeries *barSeries = new QBarSeries();
    QScatterSeries *scatterseries=new QScatterSeries();
    for (int i = 0; i < points.size(); i++) {
        lineSeries->append(points[i]);
        QBarSet *barSet = new QBarSet(QString("%1,%2").arg(points[i].x()).arg(points[i].y()));
        barSeries->append(barSet);
        scatterseries->setName(QString("(%1,%2)").arg(points[i].x()).arg(points[i].y()));
        scatterseries->append(points[i]);
    }

    chart->addSeries(lineSeries);
    chart->addSeries(barSeries);
    chart->addSeries(scatterseries);
    chart->createDefaultAxes();
    chart->setTitle("My Chart");
    QChartView *chartView = new QChartView(chart);
    chartView->setRenderHint(QPainter::Antialiasing);
    chartView->show();
}


void MainWindow::on_actionShowalltable_triggered()    //菜单“查询-显示所有表”
{
    QSqlQuery query;
    query.exec("SHOW TABLES");//这是用于mysql数据库的，别的数据库不支持
    QString table[100];
    int i=0;
    while (query.next()) {
        QString tableName = query.value(0).toString();
        QString str=QString(tableName);
        table[i]=str;
        i=i+1;
    }
    QStandardItemModel *model=new QStandardItemModel(ui->tableViewReturntable);
    for(int j=0;j<=i-1;j++)
    {
      model->setItem(j,0,new QStandardItem(table[j]));
    }
    ui->tableViewReturntable->setModel(model);
}


void MainWindow::on_actionShowallviewgarph_triggered()    //菜单“查询-显示所有视图”
{
    QSqlQuery query;
    query.exec("SHOW FULL TABLES WHERE Table_type = 'VIEW'");//这是用于mysql数据库的，别的数据库不支持
    QString table[100];
    int i=0;
    while (query.next()) {
      QString tableName = query.value(0).toString();
      QString str=QString(tableName);
      table[i]=str;
      i=i+1;
    }
    QStandardItemModel *model=new QStandardItemModel(ui->tableViewReturntable);
    for(int j=0;j<=i-1;j++)
    {
      model->setItem(j,0,new QStandardItem(table[j]));
    }
    ui->tableViewReturnviewgraph->setModel(model);
}


void MainWindow::on_pushButtonpage0confirm_clicked()    //创建数据库的按钮槽函数
{
    QSqlQuery query;
    QString databasename = ui->lineEditdatabasename->text();
    QString ty=QString("CREATE DATABASE %1 ").arg(databasename);
    query.exec (ty);
}


void MainWindow::on_pushButtonpage2confirm_clicked()  //菜单“按需查询”
{
    QString select = ui->lineEditselect->text();
    QString from = ui->lineEditfrom->text();
    QString where = ui->lineEditwhere->text();   //这三行获取查询的条件
    if (select!='*')    //将以“*”的全部查询与部分字段的查询分两种情况
    {
      QString inqury=QString("select %1 from %2 where %3").arg(select).arg(from).arg(where);
      QSqlQueryModel *model = new QSqlQueryModel;
      model->setQuery(inqury);
      ui->tableViewforoutput->setModel(model);
    }
    else
    {
      QString inqury=QString("select * from %1").arg(from);   //执行全部查询
      QSqlQueryModel *model = new QSqlQueryModel;
      model->setQuery(inqury);
      ui->tableViewforoutput->setModel(model);
    }
}


void MainWindow::on_pushButtonpage3confirm_clicked()    //更新数据的按钮槽函数
{
  QSqlQuery query;
    QString colum = ui->lineEditupdatecolum->text();
  QString table = ui->lineEditupdatetable->text();
    QString value = ui->lineEditupdatevalue->text();
  QString where = ui->lineEditupdatewhere->text();
    QString str = QString("UPDATE %1 SET %2=%3 WHERE %4").arg(table).arg(colum).arg(value).arg(where);
  query.exec (str);
}


void MainWindow::on_pushButtonpage6confirmdelete_clicked()   //删除对象的按钮槽函数
{
  QSqlQuery query;
  QString table = ui->lineEditpage6Getdeletetable->text();
  QString database = ui->lineEditpage6Getdeletedatabase->text();
  if(table!=nullptr)
  {
      QString deletetable = QString("DROP TABLE %1").arg(table);
      query.exec (deletetable);
  }
  if(database!=nullptr)
  {
      QString deletedatabase = QString("DROP DATABASE %1").arg(database);
      query.exec (deletedatabase);
  }
}


void MainWindow::on_pushButtonpage5confirmdelete_clicked()   //删除数据的按钮槽函数
{
  QString gettable = ui->lineEditpage5gettable->text();
  QString deletewhere = ui->lineEditpage5deletewhere->text();
  QSqlQuery query;
  QString deletetable = QString("DELETE FROM %1 where %2").arg(gettable).arg(deletewhere);
  if(!(gettable.isEmpty())&&!(deletewhere.isEmpty()))
  {
    query.exec(deletetable);
  }
}


void MainWindow::on_actionDeletedata_triggered()    //菜单“删除-删除数据”点击事件
{
  ui->stackedWidget->show();
  ui->stackedWidget->setCurrentIndex(5);
}


void MainWindow::on_pushButtonpage1newadd_clicked()   //新建表的按钮“新增”槽函数
{
    QAbstractItemModel * model = ui->tableViewCreatetable->model();
    if (!model)
    {
    qDebug() << "no table model";
    return;
    }

    // 行数
    int rowCount = model->rowCount();
    // 插入一行
    model->insertRow(rowCount);
}


void MainWindow::on_pushButtonpage1confirm_clicked()    //新建表的按钮“确定”槽函数
{
    // 获取表名
    QString tableName = ui->lineEditpage1gettablename->text().trimmed();
    if (tableName.isEmpty())
    {
    QMessageBox::information(this, QStringLiteral("提示"), QStringLiteral("请输入表名"));
    return;
    }

    QAbstractItemModel * model = ui->tableViewCreatetable->model();
    if (!model)
    {
    qDebug() << "no table model";
    return;
    }

    // 行数
    int rowCount = model->rowCount();
    if (rowCount <= 0)
    {
    QMessageBox::information(this, QStringLiteral("提示"), QStringLiteral("请输入字段信息"));
    return;
    }

    QVector<FieldInfo> tableInfos;
    // 读取输入的字段信息
    for (int i = 0; i < rowCount; i++)
    {
    FieldInfo fi;
    // 字段名
    fi.fieldName = model->index(i, 0).data().toString();
    // 数据类型
    fi.dataType = model->index(i, 1).data().toString();
    // 数据长度
    fi.dateLen = model->index(i, 2).data().toInt();
    // 是否可以为空
    fi.isNull = model->index(i, 3).data().toBool();
    // 是否为主键
    fi.isPrimaryKey = model->index(i, 4).data().toBool();
    // 外键/表名
    fi.foreignKeyTable = model->index(i, 5).data().toString();
    // 是否为字段
    fi.foreignKeyField = model->index(i, 6).data().toString();

    tableInfos.append(fi);
    }

    if (tableInfos.count() > 0)
    {
    // 建表
    QString sql;
    sql.append(QString("CREATE TABLE %1(").arg(tableName));

    // 添加字段
    for (int i = 0; i < tableInfos.count(); i++)
    {
        // 字段信息
        FieldInfo fi = tableInfos.at(i);
        sql.append(QString("%1 %2(%3) %4 %5").arg(fi.fieldName).arg(fi.dataType).arg(fi.dateLen)
                       .arg(fi.isNull?"":"NOT NULL").arg(fi.isPrimaryKey?"PRIMARY KEY":""));
        // 最后一个字段末尾不加逗号
        if (i != tableInfos.count()-1)
            sql.append(",");
    }

    // 添加外键(只能添加一个)
    for (int i = 0; i < tableInfos.count(); i++)
    {
        // 字段信息
        FieldInfo fi = tableInfos.at(i);
        if (!fi.foreignKeyTable.isEmpty() && !fi.foreignKeyField.isEmpty())
        {
            sql.append(QString(",FOREIGN KEY(%1) REFERENCES %2(%3)")
                           .arg(fi.fieldName).arg(fi.foreignKeyTable).arg(fi.foreignKeyField));
            break;
        }
    }

    sql.append(");");

    qDebug()<<"sql +"<<sql;

    // 执行sql
    QSqlQuery query;
    if (query.exec(sql))
        qDebug() << "create table success";

    else
        qDebug() << "failed to create table";
    }
}


void MainWindow::on_pushButtonpage4confirminsert_clicked()   //插入数据的按钮槽函数
{
    // 表名
    QString tableName = ui->lineEditpage4gettable->text().trimmed();
    if (tableName.isEmpty())
    {
    qDebug() << "tableName is empty";
    return;
    }
    // 字段名
    QString fieldName = ui->lineEditpage4getcolum->text().trimmed();
    if (fieldName.isEmpty())
    {
    qDebug() << "fieldName is empty";
    return;
    }
    // 值
    QString value = ui->lineEditpage4getvalue->text().trimmed();
    if (value.isEmpty())
    {
    qDebug() << "value is empty";
    return;
    }

    QString sql = QString("INSERT INTO %1(%2)  values %3").arg(tableName).arg(fieldName).arg(value);
    QSqlQuery query;
    if(query.exec(sql))
    qDebug() << "insert value success";
    else
    qDebug() << "falied to insert value";
}


void MainWindow::on_actionInsertdata_triggered()    //菜单“插入-插入数据”
{
    ui->stackedWidget->show();
    ui->stackedWidget->setCurrentIndex(4);
}


void MainWindow::on_pushButtonStopconnect_clicked()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.close();
    QMessageBox::information(this, "数据库已断开", "数据库已断开");
}

